package moe.util;

import java.util.Iterator;
import java.util.Map;

public class QueryBuilder {
	public static final Object PLACEHOLDER = new Object() {
		@Override
		public String toString() {
			return "?";
		}
	};

	private StringBuilder from, order, projection, where, group, limit;

	public QueryBuilder(String entityName, String alias) {
		from(entityName, alias);
	}

	public QueryBuilder from(String entityName, String alias) {
		if (entityName == null || entityName.equals("")) {
			throw new IllegalArgumentException("entityName unable is null or empty string");
		}
		if (from == null) {
			from = new StringBuilder();
		} else {
			from.append(", ");
		}
		from.append("`").append(entityName).append("`");
		if (alias != null && !"".equals(alias)) {
			from.append(" AS `").append(alias).append("`");
		}
		return this;
	}

	public QueryBuilder order(String propertyName, boolean ascending) {
		if (propertyName == null || propertyName.equals("")) {
			throw new IllegalArgumentException("propertyName unable is null or empty string");
		}
		if (order == null) {
			order = new StringBuilder();
		}
		if (order.length() > 0) {
			order.append(", ");
		}
		order.append("`").append(propertyName).append("`");
		if (!ascending) {
			order.append(" DESC");
		}
		return this;
	}

	public QueryBuilder group(String propertyName) {
		if (propertyName == null || propertyName.equals("")) {
			throw new IllegalArgumentException("propertyName unable is null or empty string");
		}
		if (group == null) {
			group = new StringBuilder();
		}
		if (group.length() > 0) {
			group.append(", ");
		}
		group.append("`").append(propertyName).append("`");

		return this;
	}

	public QueryBuilder projection(String function, String propertyName, String alias,
			boolean distinct) {
		if (propertyName == null || propertyName.equals("")) {
			throw new IllegalArgumentException("propertyName unable is null or empty string");
		}
		if (projection == null) {
			projection = new StringBuilder();
		}
		if (projection.length() > 0) {
			projection.append(", ");
		}
		if (distinct && projection.indexOf("DISTINCT ") == -1) {
			projection.insert(0, "DISTINCT ");
		}
		boolean fun = false;
		if (function != null && !function.equals("")) {
			projection.append(function).append("(");
			fun = true;
		}
		if (propertyName.equals("*")) {
			projection.append(propertyName);
		} else {
			projection.append("`").append(propertyName).append("`");
		}
		if (fun) {
			projection.append(")");
		}
		if (alias != null && !"".equals(alias)) {
			projection.append(" AS `").append(alias).append("`");
		}

		return this;
	}

	public QueryBuilder projection(String propertyName, String alias) {
		return projection(null, propertyName, alias, false);
	}

	public QueryBuilder where(String expression) {
		if (expression == null) {
			return this;
		}
		if (where == null) {
			where = new StringBuilder();
		}
		where.append("(").append(expression).append(")");
		return this;
	}

	public QueryBuilder where(Map<String, Object> queryParamValues) {
		if (queryParamValues == null || queryParamValues.isEmpty()) {
			return this;
		}
		if (where == null) {
			where = new StringBuilder();
		}
		where.append("(");
		for (Iterator<String> i = queryParamValues.keySet().iterator(); i.hasNext();) {
			String key = i.next();
			eq(key, queryParamValues.get(key));
			if (i.hasNext()) {
				and();
			}
		}
		where.append(")");
		return this;
	}

	public QueryBuilder where(String propertyName, Object value, String operator) {
		if (propertyName == null || propertyName.equals("")) {
			throw new IllegalArgumentException("key unable is null or empty string");
		}
		if (where == null) {
			where = new StringBuilder();
		}
		if (propertyName.contains(" ")) {
			where.append(propertyName);
		} else {
			where.append("`").append(propertyName).append("`");
		}
		where.append(" ").append(operator).append(" ");
		if (isString(value)) {
			value = ((String) value).replace("'", "''");
			value = "'" + value + "'";
		}
		where.append(value.toString());
		return this;
	}

	public QueryBuilder sub() {
		if (where == null) {
			where = new StringBuilder();
		}
		where.append("(");
		return this;
	}

	public QueryBuilder end() {
		if (where == null) {
			where = new StringBuilder();
		}
		where.append(")");
		return this;
	}

	public QueryBuilder and() {
		if (where == null || where.length() < 1) {
			return this;
			// throw new IllegalStateException("WHERE expression is empty");
		}
		where.append(" AND ");
		return this;
	}

	public QueryBuilder or() {
		if (where == null || where.length() < 1) {
			return this;
			// throw new IllegalStateException("WHERE expression is empty");
		}
		where.append(" OR ");
		return this;
	}

	public QueryBuilder eq(String propertyName, Object value) {
		return where(propertyName, value, "=");
	}

	public QueryBuilder neq(String propertyName, Object value) {
		return where(propertyName, value, "!=");
	}

	public QueryBuilder gt(String propertyName, Number value) {
		return where(propertyName, value, ">");
	}

	public QueryBuilder gt(String propertyName, Object value) {
		return where(propertyName, value, ">");
	}

	public QueryBuilder ge(String propertyName, Number value) {
		return where(propertyName, value, ">=");
	}

	public QueryBuilder ge(String propertyName, Object value) {
		return where(propertyName, value, ">=");
	}

	public QueryBuilder lt(String propertyName, Number value) {
		return where(propertyName, value, "<");
	}

	public QueryBuilder lt(String propertyName, Object value) {
		return where(propertyName, value, "<");
	}

	public QueryBuilder le(String propertyName, Number value) {
		return where(propertyName, value, "<=");
	}

	public QueryBuilder le(String propertyName, Object value) {
		return where(propertyName, value, "<=");
	}

	public QueryBuilder like(String propertyName, Object value) {
		return where(propertyName, value, "LIKE");
	}

	public QueryBuilder notlike(String propertyName, Object value) {
		return where(propertyName, value, "NOT LIKE");
	}

	public QueryBuilder isNull(String propertyName) {
		if (propertyName == null || propertyName.equals("")) {
			throw new IllegalArgumentException("key unable is null or empty string");
		}
		if (where == null) {
			where = new StringBuilder();
		}
		where.append("`").append(propertyName).append("` IS NULL");
		return this;
	}

	public QueryBuilder notNull(String propertyName) {
		if (propertyName == null || propertyName.equals("")) {
			throw new IllegalArgumentException("key unable is null or empty string");
		}
		if (where == null) {
			where = new StringBuilder();
		}
		where.append("`").append(propertyName).append("` IS NOT NULL");
		return this;
	}

	public QueryBuilder between(String propertyName, Object value1, Object value2) {
		if (propertyName == null || propertyName.equals("")) {
			throw new IllegalArgumentException("key unable is null or empty string");
		}
		if (where == null) {
			where = new StringBuilder();
		}
		where.append("`").append(propertyName).append("` BETWEEN ");
		if (isString(value1)) {
			value1 = ((String) value1).replace("'", "''");
			value1 = "'" + value1 + "'";
		}
		if (isString(value2)) {
			value2 = ((String) value2).replace("'", "''");
			value2 = "'" + value2 + "'";
		}
		where.append(value1).append(" AND ").append(value2);
		return this;
	}

	public QueryBuilder in(String propertyName, String expression) {
		if (propertyName == null || propertyName.equals("")) {
			throw new IllegalArgumentException("key unable is null or empty string");
		}
		if (where == null) {
			where = new StringBuilder();
		}
		where.append("`").append(propertyName).append("` IN (").append(expression).append(")");
		return this;
	}

	public QueryBuilder in(String propertyName, Object... args) {
		StringBuilder expression = new StringBuilder();
		for (int i = 0, length = args.length; i < length; i++) {
			if (isString(args[i])) {
				args[i] = ((String) args[i]).replace("'", "''");
				args[i] = "'" + args[i] + "'";
			}
			expression.append(", ").append(args[i]);
		}
		in(propertyName, expression.substring(2));
		return this;
	}

	public QueryBuilder limit(Object offset, Object count) {
		limit = new StringBuilder();
		limit.append(offset).append(", ").append(count);
		return this;
	}

	public String build() {
		StringBuilder result = new StringBuilder("SELECT ");
		if (projection == null) {
			result.append("*");
		} else {
			result.append(projection);
		}
		result.append(" FROM ").append(from);
		if (where != null) {
			result.append(" WHERE ").append(where);
		}
		if (group != null) {
			result.append(" GROUP BY ").append(group);
		}
		if (order != null) {
			result.append(" ORDER BY ").append(order);
		}
		if (limit != null) {
			result.append(" LIMIT ").append(limit);
		}
		return result.toString();
	}

	public void escape(Object escapeChar) {
		if (where != null) {
			String param = escapeChar.toString();
			if (escapeChar instanceof CharSequence) {
				param = "'" + param + "'";
			}
			where.append(" ESCAPE ").append(param);
		}
	}

	private boolean isString(Object o) {
		return o instanceof String;
	}

}
